using System;
using System.Collections.Generic;
using System.Text;
using Fieldbook.Entities;
using System.Data;
using Fieldbook.Common;
using Microsoft.Practices.EnterpriseLibrary.Data.Sql;
using System.Data.Common;

namespace Fieldbook.DAL
{
    public static class StorageItemDAO
    {
        #region insert
        private static DbCommand GetInsertCommand(SqlDatabase db)
        {
            string insertSQL = "INSERT INTO dbo.Storage (RESOURCEID, HOUSEHOLDID, QUOTA) VALUES (@RESOURCEID, @HOUSEHOLDID, @QUOTA) SET @STORAGEID = SCOPE_IDENTITY()";
            DbCommand insertCommand = db.GetSqlStringCommand(insertSQL);
            db.AddOutParameter(insertCommand, "@STORAGEID", DbType.Int64, 8);
            db.AddInParameter(insertCommand, "@RESOURCEID", DbType.Int64);
            db.AddInParameter(insertCommand, "@HOUSEHOLDID", DbType.Int64);
            db.AddInParameter(insertCommand, "@QUOTA", DbType.Decimal);
            return insertCommand;
        }
        private static void SetInsertValues(SqlDatabase db, DbCommand cmd, StorageItem entity)
        {
            db.SetParameterValue(cmd, "@RESOURCEID", entity.ResourceID);
            db.SetParameterValue(cmd, "@HOUSEHOLDID", entity.HouseHoldID);
            db.SetParameterValue(cmd, "@QUOTA", entity.Quota);
        }
        public static bool InsertStorageItem(StorageItem entity)
        {
            bool res = false;
            SqlDatabase db = new SqlDatabase(AppSettingsHelper.GetConfigValue(Constants.FieldbookConnectionString));
            DbCommand cmd = GetInsertCommand(db);
            SetInsertValues(db, cmd, entity);
            res = Convert.ToBoolean(db.ExecuteNonQuery(cmd));
            if (res)
            {
                entity.StorageID = (long?)db.GetParameterValue(cmd, "@STORAGEID");
            }
            return res;
        }
        public static bool InsertStorageItem(StorageItem entity, DbTransaction transactionObj, SqlDatabase db)
        {
            bool res = false;
            DbCommand cmd = GetInsertCommand(db);
            SetInsertValues(db, cmd, entity);
            res = Convert.ToBoolean(db.ExecuteNonQuery(cmd, transactionObj));
            if (res)
            {
                entity.StorageID = (long?)db.GetParameterValue(cmd, "@STORAGEID");
            }
            return res;
        }
        #endregion

        #region update

        private static DbCommand GetUpdateQuotaCommand(SqlDatabase db)
        {
            string updateSql = "UPDATE dbo.Storage SET QUOTA = QUOTA + @QUOTA WHERE STORAGEID = @STORAGEID";
            DbCommand updateCmd = db.GetSqlStringCommand(updateSql);
            db.AddInParameter(updateCmd, "@QUOTA", DbType.Decimal);
            db.AddInParameter(updateCmd, "@STORAGEID", DbType.Int64);
            return updateCmd;
        }
        private static void SetUpdateQuotaParameters(SqlDatabase db, DbCommand cmd, decimal quota, long storageID)
        {
            db.SetParameterValue(cmd, "@QUOTA", quota);
            db.SetParameterValue(cmd, "@STORAGEID", storageID);
        }

        public static bool UpdateStorageQuota(long storageID, decimal amount)
        {
            bool res = false;
            SqlDatabase db = new SqlDatabase(AppSettingsHelper.GetConfigValue(Constants.FieldbookConnectionString));
            DbCommand cmd = GetUpdateQuotaCommand(db);
            SetUpdateQuotaParameters(db, cmd, amount, storageID);
            res = Convert.ToBoolean(db.ExecuteNonQuery(cmd));
            return res;
        }
        public static bool UpateStorageQuota(long storageID, decimal amount, DbTransaction transactionObj, SqlDatabase db)
        {
            bool res = false;
            DbCommand cmd = GetUpdateQuotaCommand(db);
            SetUpdateQuotaParameters(db, cmd, amount, storageID);
            res = Convert.ToBoolean(db.ExecuteNonQuery(cmd, transactionObj));
            return res;
        }
        #endregion

        #region select
        private static List<StorageItem> LoadStorageItems(string sql)
        {
            List<StorageItem> res = new List<StorageItem>();
            IDataReader reader = null;
            try
            {
                SqlDatabase db = new SqlDatabase(AppSettingsHelper.GetConfigValue(Constants.FieldbookConnectionString));
                reader = db.ExecuteReader(CommandType.Text, sql);
                if (reader != null)
                {
                    while (reader.Read())
                    {
                        StorageItem entity = new StorageItem();
                        entity.StorageID = reader.GetInt64(0);
                        entity.ResourceID = reader.GetInt64(1);
                        entity.HouseHoldID = reader.GetInt64(2);
                        entity.Quota = reader.GetDecimal(3);
                        res.Add(entity);
                    }
                    reader.Close();
                }
            }
            catch (Exception ex)
            {
                if (reader != null && !reader.IsClosed)
                {
                    reader.Close();
                }
                res = null;
                throw ex;
            }
            return res;
        }

        public static List<StorageItem> LaodStorageItems(long userID, long categoryID)
        {
            string selectSQL = String.Format(@"SELECT S.STORAGEID, S.RESOURCEID, S.HOUSEHOLDID, S.QUOTA FROM 
dbo.STORAGE S
JOIN HOUSEHOLD HH ON S.HOUSEHOLDID = HH.HEADID
JOIN RESOURCE R ON S.RESOURCEID = R.RESOURCEID
JOIN RESOURCECATEGORY RC ON R.CATEGORYID = RC.CATEGORYID
WHERE HH.MEMBERID = {0} AND RC.CATEGORYID = {1}", userID, categoryID);
            return LoadStorageItems(selectSQL);
        }

        public static long? GetStorageID(long resourceID, long householdID)
        {
            string sql = "SELECT STORAGEID FROM dbo.Storage WHERE RESOURCEID = @RESOURCEID AND HOUSEHOLDID = @HOUSEHOLDID";
            SqlDatabase db = new SqlDatabase(AppSettingsHelper.GetConfigValue(Constants.FieldbookConnectionString));
            DbCommand cmd = db.GetSqlStringCommand(sql);
            db.AddInParameter(cmd, "@RESOURCEID", DbType.Int64, resourceID);
            db.AddInParameter(cmd, "@HOUSEHOLDID", DbType.Int64, householdID);
            return (long?)db.ExecuteScalar(cmd);
        }
        #endregion
    }
}
